//mysql连接池配置文件
const mysql = require("mysql");
const $dbConfig = require("./dbConfig.js"); //注意改成自己项目中mysql配置文件的路径
const sql = require("./sql.js"); //
const mysqlDB = mysql.createPool($dbConfig.base); // 使用连接池，避免开太多的线程，提升性能
const execs = require('child_process').exec
const json = require("./json.js");
const exe = require('./exe.js');
const { formatDateTime } = require("./common.js");
const jwt = require("jsonwebtoken");
const Salt = require("../data/Salt.json");
const ExcelJS = require("exceljs");
//将密码进行哈希加密
const crypto = require('crypto');
let fs = require("fs");
const { setLoginLog, setOperationLog } = require("../middleware/recordLog.js");
/**
 * @description 新增一条数据
 * @param  {str} table 数据库表的名称
 * @param  {obj} req 插入的数据
 * @param  {obj} res 接口函数中的res对象
 * @param  {obj} next 接口函数中的next对象
 */
const isRunning = (query, cb) => {
  let platform = process.platform
  let cmd = ''
  switch (platform) {
    case 'win32':
      cmd = `tasklist`
      break
    case 'darwin':
      cmd = `ps -ax | grep ${query}`
      break
    case 'linux':
      cmd = `ps -A`
      break
    default:
      break
  }
  execs(cmd, (err, stdout, stderr) => {
    cb(stdout.toLowerCase().indexOf(query.toLowerCase()) > -1)
  })
}
//将前端传输过来的key解压成盐(与前端加盐对应)
function jiexi(key) {
  function is0(data) {
    if (data == 0) {
      return 0
    } else {
      return data
    }
  }
  var randomString = key.slice(0, -11)
  var randomnumber = key.slice(-11,)
  var mmm = ''
  for (var i = 0; i < randomnumber.length; i++) {
    mmm += randomString[randomnumber[is0(i)]];
  }
  return mmm
}



// sha256加密密码并验证,getpassword为前端传过来的密码
function verifyPassword(password, getpassword) {
  const hashedPassword = crypto
    .createHash('sha256')
    .update(password)
    .digest('hex');
  // 验证密码是否匹配
  if (hashedPassword === getpassword) {
    return true; // 密码正确
  } else {
    return false; // 密码错误
  }
}
// 验证用户是否存在
function verifyUser(account) {
  return new Promise((resolve, reject) => {
    var pool = mysqlDB;
    var sqlString = `select * from sys_user where NserName =?`;
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sqlString, [account], (err, result) => {
          connection.release(); // 释放连接
          if (err) {
            reject(false);
          } else {
            resolve(result); // 返回 result
          }
        });
      }
    });
  });
}
// 假设 binaryData 是你的二进制图片数据
function convertBinaryToBase64(binaryData) {
  return new Promise((resolve) => {
    const blob = new Blob([binaryData], { type: 'image/png' }); // 创建 Blob 对象
    const reader = new FileReader();
    
    reader.onloadend = () => {
      resolve(reader.result); // 返回 Base64 字符串
    };

    reader.readAsDataURL(blob); // 转换为 Base64
  });
}
// 登录操作
let login = (table, req, res) => {
  let ip = req.ip
  ip = ip.replace(/^.*:(.*)$/, '$1');
  let { account, password, key } = req.body;
  key = jiexi(key);
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].login, [account], (err, result) => {
        let msg = "登录成功"
        let status = 0
        if (err) {
          console.log(err);
          msg = "操作失败"
          status = 1
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          console.log(119, result.avatar);
          if (result != "") {
            //验证密码是否正确
            let yanzheng = verifyPassword(
              result[0].UserPassword + key,
              password
            );

            if (yanzheng) {
              const Permission = result[0].Permission;
              const userdata = {
                account: account,
                Permission: Permission,
              };
              let token = jwt.sign(userdata, Salt.Salt, {
                expiresIn: 120 * 60,
              });
              // let avatar = result[0].avatar.toString("base64") ;
              const data = {
                User: result[0].Name,
                Account: account,
                Permission: Permission,
                Email: result[0].email,
                JobNumber: result[0].JobNumber,
                Role: result[0].role,
                avatar: result[0].avatar,
              };
              console.log(144,data)
              // 以json形式，把操作结果返回给前台页面
              res.json({
                code: 200,
                msg: msg,
                data: data,
                token: token,
              });
            } else {
              msg = "密码错误"
              status = 1
              res.json({
                code: 300,
                msg: msg,
                data: [],
              });
            }
          } else {
            msg = "用户不存在"
            status = 1
            res.json({
              code: 300,
              msg: msg,
              data: [],
            });
          }
        }
        const params = [account, ip, status, msg]
        setLoginLog(params)
        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });

};
// 退出登录操作
let loginOut = (table, req, res) => {
  let ip = req.ip
  ip = ip.replace(/^.*:(.*)$/, '$1');
  let { account } = req.body;
  const params = [account, ip, 0, '退出成功']
  setLoginLog(params)
  res.json({
    code: "200",
    msg: "退出成功",
  });
};
// 获取操作权限
let getOperationPermissions = (table, req, res) => {
  let { Permission } = req.user;
  Permission = Permission.split(",");
  if (Permission.includes('admin')) {
    let result = {
      result: "selectall",
      data: ["*:*:*"],
    };
    return json(res, result, '');
  }
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].getOperationPermissions,
        [Permission],
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result.length >= 0) {
              var _result = result.map((item) => {
                return item.perms;
              });
              result = {
                result: "selectall",
                data: _result,
              };
            } else {
              result = undefined;
            }
            global.result = result;

            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 获取路由
let getRoutes = (table, req, res) => {
  // 带参数筛选，不带参数全选
  let { permission } = req.body;
  let { account } = req.user
  let where = ``;
  let where2 = ` JOIN sys_role_menu rp ON r.menu_id = rp.menu_id JOIN sys_role ro ON rp.role_id = ro.role_id WHERE  ro.status = 0 and r.menu_type != 'F'  and ro.role_key in (?)  `;
  let paraArr = []
  if (permission) {
    permission = permission.split(",");
    if (!permission.includes('admin')) {
      where += where2;
      paraArr.push(permission);
    } else {
      where += ` where  r.menu_type != 'F' `;
    }

  } else { }

  where += `GROUP BY r.menu_id  order by r.parent_id,r.order_num`;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].getRoutes + where,
        paraArr,
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result.length > 0) {
              var _result = result;
              // 生成路由对象
              const routes = [];
              const routeMap = {};
              // 将所有路由按 menuId 保存到 map 中
              _result.forEach((row) => {
                if (!routeMap.hasOwnProperty(row.menuId)) {
                  routeMap[row.menuId] = {
                    menuId: row.menuId,
                    parentId: row.parentId,
                    path: row.path,
                    name: row.name,
                    component: row.component,
                    requiresAuth: !!row.requiresAuth, // 转换为布尔值
                    menuName: row.menuName,
                    icon: row.icon,
                    createBy: row.createBy,
                    createTime: row.createTime,
                    updateBy: row.updateBy,
                    updateTime: row.updateTime,
                    remark: row.remark,
                    orderNum: row.orderNum,
                    query: row.query,
                    isFrame: row.isFrame,
                    isCache: row.isCache,
                    menuType: row.menuType,
                    visible: row.visible,
                    status: row.status,
                    perms: row.perms,
                    affix: row.path == "/index",
                    children: [],
                  };
                } else {
                  let elem = routeMap[row.menuId];
                  (elem.menuId = row.menuId),
                    (elem.parentId = row.parentId),
                    (elem.path = row.path),
                    (elem.name = row.name),
                    (elem.component = row.component),
                    (elem.requiresAuth = !!row.requiresAuth), // 转换为布尔值
                    (elem.menuName = row.menuName),
                    (elem.icon = row.icon),
                    (elem.createBy = row.createBy),
                    (elem.createTime = row.createTime),
                    (elem.updateBy = row.updateBy),
                    (elem.updateTime = row.updateTime),
                    (elem.remark = row.remark),
                    (elem.orderNum = row.orderNum),
                    (elem.query = row.query),
                    (elem.isFrame = row.isFrame),
                    (elem.isCache = row.isCache),
                    (elem.menuType = row.menuType),
                    (elem.visible = row.visible),
                    (elem.status = row.status),
                    (elem.perms = row.perms),
                    (elem.affix = row.path == "/index");
                }
                // 判断是否是顶级路由
                if (row.parentId === 0) {
                  routes.push(routeMap[row.menuId]);
                } 
                else {
                  if (!routeMap.hasOwnProperty(row.parentId)) {
                    routeMap[row.parentId] = { children: [] };
                  }
                  // 如果有 parentId children
                  routeMap[row.parentId].children.push(routeMap[row.menuId]);
                }
              });
              result = {
                result: "selectall",
                data: routes,
              };
              json(res, result, err);
            } else {
              res.json({
                code: "300",
                msg: `该用户${account} 所属权限${rolesArray.toString()}被删除或禁用`,
              });
            }
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
//获取某个菜单
let getMenu = (table, req, res) => {
  const idParam = req.params.id;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].getMenu, [idParam], (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result.length >= 0) {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
}
//更新菜单
let updateMenu = (table, req, res) => {
  let account = req.user.account;
  const {
    path,
    name,
    component,
    requiresAuth,
    menuName,
    icon,
    parentId,
    remark,
    orderNum,
    query,
    isFrame,
    isCache,
    menuType,
    visible,
    status,
    perms,
    menuId,
  } = req.body;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].updateMenu,
        [
          path,
          menuId,
          path,
          menuId,
          path,
          name,
          component,
          requiresAuth,
          menuName,
          icon,
          parentId,
          account,
          remark,
          orderNum,
          query,
          isFrame,
          isCache,
          menuType,
          visible,
          status,
          perms,
          menuId,
        ],
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result.length>0 ) {
              if (result[0][0].diff == 1) {
                let msg = "路由地址已存在！"
                // 更新操作日志
                setOperationLog(req, "update", msg);
                res.json({
                  code: "300",
                  msg: msg,
                });
              } else {
                // 更新操作日志
                setOperationLog(req, "update");
                json(res, "update", err);
              }
            } else {
              json(res, "undefined", err);
            }
            
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 删除菜单
let delMenu = (table, req, res) => {
  let { menuId } = req.body;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].delMenu,
        [menuId, menuId, menuId],
        async (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "delete";
            } else {
              result = undefined;
            }
            // 更新操作日志
            setOperationLog(req,  result);
            global.result = result;

            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
//新增菜单
let addMenu = (table, req, res) => {
  let account = req.user.account;
  const {
    path,
    name,
    component,
    requiresAuth,
    menuName,
    icon,
    parentId,
    remark,
    orderNum,
    query,
    isFrame,
    isCache,
    menuType,
    visible,
    status,
    perms
  } = req.body;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].addMenu,
        [
          path,
          name,
          component,
          requiresAuth,
          menuName,
          icon,
          parentId,
          remark,
          orderNum,
          query,
          isFrame,
          isCache,
          menuType,
          visible,
          status,
          perms,
          account
        ],
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "add";
            } else {
              result = undefined;
            }
            // 更新操作日志
            setOperationLog(req,  result);
            global.result = result;
            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 获取所有路由及权限
let listRole = (table, params, res) => {
  let {
    queryParams: { roleName, roleKey, status },
    dateRange,
  } = params;
  if (!dateRange) dateRange = []
  let startTime = dateRange[0] ? ` and create_time>='${dateRange[0]}'` : ''
  let endTime = dateRange[1] ? ` and create_time<='${dateRange[1]}'` : "";
  let status2 = status ? ` and status = '${status}' ` : ''
  var pool = mysqlDB;
  let where = ` and role_name like '%${roleName}%' and role_key like '%${roleKey}%' ${startTime} ${endTime} ${status2}  order by role_sort`;
  let sqlString = sql[table].listRole + where;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result.length >= 0) {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 将角色定义成数学结构
function buildTree(arr, parentId = 0) {
  return arr
    .filter((item) => item.parent_id === parentId)
    .map((item) => {
      const children = buildTree(arr, item.menu_id);
      return {
        id: item.menu_id,
        label: item.menu_name,
        ...(children.length > 0 && { children }),
      };
    });
}

// 获取所有的角色菜单
let roleMenuTreeselect = (table, params, res) => {
  let { roleId } = params;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].roleMenuTreeselect,
        [roleId, roleId],
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              const tree = buildTree(result[1]);
              var _result = { checkedKeys: result[0], menus: tree };
              result = {
                result: "selectall",
                data: _result,
              };
            } else {
              result = undefined;
            }
            global.result = result;

            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 注册操作
let register = async (table, params, res) => {
  let { account, password, username } = params;
  var pool = mysqlDB;
  var permission = 'user'
  var role_ids = "2";
  var role = "普通角色";
  let exist = await verifyUser(account);
  if (exist != false) {
    res.json({
      code: "300",
      msg: "用户已存在",
    });
    return
  }
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].register,
        [account, password, permission, role_ids,role, username],
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              res.json({
                code: "200",
                msg: "注册成功",
              });
            } else {
              res.json({
                code: "300",
                msg: "操作失败:",
              });
            }
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
}
//查询所有用户信息  ------------------------------------
let getAccount = (table, params, res) => {
  let { account, name, time } = params
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].getAccount, [account, name, time], (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
let getRole = (table, params, res) => {
  let { roleId } = params;
  var pool = mysqlDB;
  let where = ` and role_id = ?  order by role_sort`;
  let sqlString = sql[table].listRole + where
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, [roleId], (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
let getUserRole = (table, res) => {
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].getRole, (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 更新角色
let updateRole = async (table, req, res) => {
  let role = req.user.Permission;
  let { roleId, menuIds, roleKey, roleName, roleSort, status, remark } =
    req.body;
  let roleList = await checkRoleUnique(table, req, roleId);
  if (roleList.length > 0) {
    let err = {
      code: "300",
      msg: "失败：角色名或角色权限已存在",
    };
    // 更新操作日志
    setOperationLog(req,'update', err);
    return res.json(err);
  }
  let arr = menuIds.map((item) => {
    return [roleId, item];
  })
  remark = remark ? remark : "";
  let params = [
    roleId,
    roleName,
    roleKey,
    roleSort,
    status,
    role,
    remark,
    roleId,
  ]
  let insertSql = ''
  if (arr.length > 0) {
    insertSql = `insert IGNORE  into sys_role_menu(role_id, menu_id) values ?;`
    params = [
      roleId,
      arr,
      roleName,
      roleKey,
      roleSort,
      status,
      role,
      remark,
      roleId,
    ];
  }
  let sqlString =
    sql[table].updateRole + insertSql +
    `update sys_role set role_name=?,role_key=?,role_sort=?,status=?,update_time= now(),update_by=?,remark=? where role_id = ?`;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sqlString,
        [...params],
        async (err, result) => {
          if (err) {
            console.log(err);
            // 更新操作日志
            setOperationLog(req, undefined);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "update";
            } else {
              result = undefined;
            }
            // 更新操作日志
            setOperationLog(req,  result);
            global.result = result;
            let res2 = await updateUserPermission(table);
            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 新增角色
let addRole = async (table, req, res) => {
  let { roleId, menuIds } =
    req.body;
  let arr = menuIds.map((item) => {
    return [roleId, item];
  });
  let sqlString =
    sql[table].updateRole +
    `insert IGNORE  into sys_role_menu(role_id, menu_id) values ?;`;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, [roleId, arr], (err, result) => {
        if (err) {
          console.log(err);
          // 更新操作日志
          setOperationLog(req, undefined);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            result = "add";
          } else {
            result = undefined;
          }
          global.result = result;
          // 更新操作日志
          setOperationLog(req, result);
          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
//更新用户的角色ids
let updateUserIds = (roleID) => {
  return new Promise((resolve, reject) => {
    var pool = mysqlDB;
    let sqlString = `UPDATE sys_user SET role_ids = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', role_ids, ','), ',${roleID},', ',')) WHERE FIND_IN_SET('${roleID}', role_ids) > 0;`;
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sqlString, (err, result) => {
          connection.release(); // 释放连接
          if (err) {
            reject(false);
          } else {
            resolve(result); // 返回 result
          }
        });
      }
    });
  });
};
let delRole = (table, req, res) => {
  let { roleId } = req.body;
  let { Permission } = req.user;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].delRole,
        [roleId, Permission, roleId],
        async (err, result) => {
          if (err) {
            console.log(err);
            // 更新操作日志
            setOperationLog(req, undefined);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "delete";
              roleId.forEach(async (item) => {
                item = item.toString();
                let res2 = await updateUserIds(item);
              });
            } else {
              result = undefined;
            }
            global.result = result;
            // 更新操作日志
            setOperationLog(req, result);
            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 验证角色名或角色权限是否存在
let checkRoleUnique = (table, req, roleId) => {
  return new Promise((resolve, reject) => {
    let { roleName, roleKey } = req.body;
    let sqlString = sql[table].checkRoleUnique
    if (roleId) {
      sqlString += ` and role_id != ${roleId} `
    }
    var pool = mysqlDB;
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sqlString, [roleName, roleKey], (err, result) => {
          connection.release(); // 释放连接
          if (err) {
            reject(false);
          } else {
            resolve(result); // 返回 result
          }
        });
      }
    });
  });
};
let addRoleName = async (table, req, res, next) => {
  let {
    roleName,
    roleKey,
    roleSort,
    status,
    menuCheckStrictly,
    deptCheckStrictly,
    remark,
  } = req.body;
  let { Permission } = req.user;
  let roleList = await checkRoleUnique(table, req);
  if (roleList.length > 0) {
    let err = {
      code: "300",
      msg: "失败：角色名或角色权限已存在",
    };
    // 更新操作日志
    setOperationLog(req,'update', err);
    return res.json(err);
  }
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].addRoleName,
        [
          roleName,
          roleKey,
          roleSort,
          status,
          menuCheckStrictly,
          deptCheckStrictly,
          remark,
          Permission,
          Permission,
        ],
        async (err, result) => {
          if (err) {
            console.log(err);
            return res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            let roleList2 = await checkRoleUnique(table, req);
            req.body.roleId = roleList2[0].role_id;
          }
          connection.release();
          next();
        }
      );
    } else {
      console.log(err);;
      json(res, "444", err);
      next();
    }

  });
};
let changeRoleStatus = async (table, req, res, next) => {
  let { roleId, status } = req.body;
  let { Permission } = req.user;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].changeRoleStatus,
        [status, Permission, roleId],
        async (err, result) => {
          if (err) {
            console.log(err);
            return res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "update";
            } else {
              result = undefined;
            }
            global.result = result;
            // 更新操作日志
            setOperationLog(req, result);
            json(res, result, err);
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
      next();
    }
  });
};
let getAllRoute = (table, res) => {
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].getAllRoute, (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 更新账号信息
let EditAccount = (table, req, res) => {
  let { data: { JobNumber, Name, email, enable, NserName, roleIds } } = req.body
  roleIds = roleIds.join(",");
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].EditAccount,
        [JobNumber, Name, email, enable, roleIds, NserName],
        async (err, result) => {
          if (err) {
            console.log(err);
            // 更新操作日志
            setOperationLog(req, undefined);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "update";
              await updateUserPermission(table);
            } else {
              result = undefined;
            }
            global.result = result;
            // 更新操作日志
            setOperationLog(req, result);
            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
}
//更新用户的角色key和name
let updateUserPermission = (table) => {
  return new Promise((resolve, reject) => {
    var pool = mysqlDB;
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sql[table].updateUserPermission, (err, result) => {
          connection.release(); // 释放连接
          if (err) {
            reject(false);
          } else {
            resolve(result); // 返回 result
          }
        });
      }
    });
  });
};

// 删除账号信息
let DeleteAccount = (table, req, res) => {
  let { NserName } = req.body;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].DeleteAccount, [NserName], (err, result) => {
          if (err) {
            console.log(err);
            // 更新操作日志
            setOperationLog(req, undefined);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "delete";
            } else {
              result = undefined;
            }
            global.result = result;
            // 更新操作日志
            setOperationLog(req, result);

            json(res, result, err);
          }

          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 修改用户信息;
let UpdateUserinfo = (table, params, res) => {
  let { account, userName, permission, email, jobNumber } = params;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].UpdateUserinfo,
        [userName, permission, email, jobNumber, account],
        (err, result) => {
          if (err) {
            console.log(err);
            // 更新操作日志
            setOperationLog(req, undefined);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              result = "update";
            } else {
              result = undefined;
            }
            global.result = result;
            // 更新操作日志
            setOperationLog(req, result);
            json(res, result, err);
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
}
// 获取用户基础信息
let getUser = (table, params, res) => {
  const { account } = params;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sql[table].getUser, [account], (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            var _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          global.result = result;

          json(res, result, err);
        }

        connection.release();
      });
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 修改密码;
let updatepwd = (table, req, res) => {
  let { old_pwd, new_pwd, account } = req.body;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].updatepwd,
        [new_pwd, account, old_pwd],
        (err, result) => {
          let _result = undefined;
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              if (result.affectedRows > 0) {
                _result = "update";
                // 更新操作日志
                setOperationLog(req,  _result);
              } else {
                // 更新操作日志
                setOperationLog(req, _result);
                res.json({
                  code: "300",
                  msg: "密码不正确",
                });
                return;
              }
            }
            result = _result;
            json(res, result, err);
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 修改头像;
let updateAvatar = (table, req, res) => {
  let { avatar, fileName } = req.body;
  const account = req.user.account
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sql[table].updateAvatar,
        [avatar, account],
        (err, result) => {
          let _result = undefined;
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              req.body = {fileName}
              if (result.affectedRows > 0) {
                _result = "add";
                // 更新操作日志
                setOperationLog(req, _result, fileName);
              } else {
                // 更新操作日志
                setOperationLog(req, _result, fileName);
                _result = undefined;
                return;
              }
            }
            result = _result;
            json(res, result, err);
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 获取登录日志
let getlogininfor = (table, req, res) => {
  let { pageNum, pageSize, ipaddr, userName, status, startTime, endTime } = req.body;
  let where = ` `
  if (ipaddr) {
    where += ` and ipaddr like '%${ipaddr}%' `
  }
  if (userName) {
    where += `  and user_name like '%${userName}%' `
  }
  if (status) {
    where += `   and status = ${status} `
  }
  if (startTime & endTime) {
    where += `   and access_time BETWEEN ${startTime}  and ${endTime}  `;
  }
  let where2 = where + `  order by access_time desc limit ${(pageNum - 1) * pageSize},${pageSize};`
  const sqlString1 = `SELECT COUNT(*) AS totalCount FROM sys_loginlog where 1=1 ` + where
  const sqlString = sql[table].getlogininfor + where2 + sqlString1
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sqlString,
        (err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            let _result = undefined;
            if (result != "") {
              _result = result;
              result = {
                result: "selectall",
                data: _result,
              };
            } else {
              result = undefined;
            }
            json(res, result, err);
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
let deleteLoginInfo = (table, req, res) => {
  const infoId = req.params.infoId.split(",");
  let where = ` `;
  if (infoId[0] != "undefined") {
    where += ` where info_id in (?) `;
  }
  const sqlString = sql[table].deleteLoginInfo + where;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString,[infoId], (err, result) => {
        if (err) {
          console.log(err);
          setOperationLog(req, undefined);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            result = "delete";
          } else {
            result = undefined;
          }
          global.result = result;
          // 更新操作日志
          setOperationLog(req, result);

          json(res, result, err);
        }
        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 获取操作日志
let getOperationLog = (table, req, res) => {
  let { pageNum, pageSize, title, operName,businessType, status, startTime, endTime } =
    req.body;
  let where = ` `;
  if (title) {
    where += ` and title like '%${title}%' `;
  }
  if (operName) {
    where += `  and oper_name like '%${operName}%' `;
  }
  if (businessType) {
    where += `   and business_type = ${businessType} `;
  }
  if (status) {
    where += `   and status = ${status} `;
  }
  if (startTime & endTime) {
    where += `   and oper_time BETWEEN ${startTime}  and ${endTime}  `;
  }
  let where2 =
    where +
    `  order by oper_time desc limit ${(pageNum - 1) * pageSize},${pageSize};`;
  const sqlString1 =
    `SELECT COUNT(*) AS totalCount FROM sys_operlog where 1=1 ` + where;
  const sqlString = sql[table].getOperationLog + where2 + sqlString1;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          let _result = undefined;
          if (result != "") {
            _result = result;
            result = {
              result: "selectall",
              data: _result,
            };
          } else {
            result = undefined;
          }
          json(res, result, err);
        }
        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
let deleteOperLog = (table, req, res) => {
  const infoId = req.params.infoId.split(",");
  let where = ` `;
  if (infoId[0] != "undefined") {
    where += ` where oper_id in (?) `;
  }
  const sqlString = sql[table].deleteOperLog + where;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, [infoId], (err, result) => {
        if (err) {
          console.log(err);
          setOperationLog(req, undefined);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            result = "delete";
          } else {
            result = undefined;
          }
          global.result = result;
          // 更新操作日志
          setOperationLog(req, result);

          json(res, result, err);
        }
        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
// 将列表数据转成excel表格二进制
async function dataToExcel(sheetName, headers, dataArr) {
  if (dataArr.length > 0) {
    sheetName = sheetName ? sheetName : "Sheel1";
    // heardNameDict = heardNameDict ? heardNameDict : {};
    // 创建新的工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet(sheetName);
    // 设置列标题
    worksheet.columns = headers;
    // const headers = Object.keys(dataArr[0]);
    // worksheet.columns = headers.map((header) => ({
    //   header: heardNameDict[header] ? heardNameDict[header] : header, // 标题栏文字
    //   key: header, // key
    //   width: 20, // 设置列宽
    // }));
    //设置列居中
    worksheet.columns.forEach((item, i) => {
      worksheet.getColumn(i + 1).alignment = {
        vertical: "middle",
        horizontal: "center",
      };
    });
    // 添加数据行
    dataArr.forEach((row) => {
      worksheet.addRow(row);
    });
    // 设置首行的样式
    const headerRow = worksheet.getRow(1);
    headerRow.eachCell((cell) => {
      cell.font = {
        bold: true,
        color: { argb: "FFFFFF" }, // 字体颜色为白色
        size: 14,
      };
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "adacac" }, // 背景色为灰色
      };
      cell.alignment = {
        horizontal: "center", // 水平居中
        vertical: "middle", // 垂直居中
      };
    });
    // 设置首行高度
    headerRow.height = 30; // 设置行高为30
    // 设置其他行的样式
    worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
      if (rowNumber > 1) {
        // 从第二行开始
        row.alignment = { vertical: "middle", horizontal: "center" };

        row.height = 25; // 设置其他行的高度（可选）
        // 设置行内字体样式（可选）
        row.eachCell((cell) => {
          cell.font = {
            size: 12, // 设置字体大小为12
          };
          cell.border = {
            // 设置边框
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          };
        });
      }
    });
    // 生成 Excel 文件的 Buffer
    const buffer = await workbook.xlsx.writeBuffer();
    return buffer;
  } else {
    return "没有数据下载";
  }
}
// 导出登录日志excel表
let exportLoginLogExcel = (table, req, res) => {
  let { pageNum, pageSize, ipaddr, userName, status, startTime, endTime } =
    req.body;
  let where = ` `;
  if (ipaddr) {
    where += ` and ipaddr like '%${ipaddr}%' `;
  }
  if (userName) {
    where += `  and user_name like '%${userName}%' `;
  }
  if (status) {
    where += `   and status = ${status} `;
  }
  if (startTime & endTime) {
    where += `   and access_time BETWEEN ${startTime}  and ${endTime}  `;
  }
  let where2 = where + `  order by access_time desc ;`
  const sqlString = sql[table].getlogininfor + where2; 
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(
        sqlString,
        async(err, result) => {
          if (err) {
            console.log(err);
            res.json({
              code: "300",
              msg: "操作失败:",
            });
          } else {
            if (result != "") {
              const sheetName = "Sheel1";
              const heards = [
                { header: '序号', key: 'infoId', width: 15 },
                { header: '地址', key: 'ipaddr', width: 20 },
                { header: '用户名称', key: 'userName', width: 20 },
                { header: '状态', key: 'status', width: 15 },
                { header: '描述', key: 'msg', width: 20 },
                { header: '访问时间', key: 'accessTime', width: 24 },
              ]
              result.map(item => {
                item.accessTime = formatDateTime(item.accessTime);
                item.status = item.status=1?'成功':'失败';
                return item
              })
              // // 将列表数据转成excel表格二进制
              let buffer = await dataToExcel(sheetName, heards, result);
              // 直接保存文件
              // fs.writeFileSync("./data4.xlsx", buffer);
              res.send(buffer);
            } else {
              res.send("没有数据下载");
            }
          }
          connection.release();
        }
      );
    } else {
      console.log(err);
      ;
      json(res, "444", err);
    }
  });
};
// 导出操作日志excel表
let exportOperLogExcel = (table, req, res) => {
  let {
    pageNum,
    pageSize,
    title,
    operName,
    businessType,
    status,
    startTime,
    endTime,
  } = req.body;
  let where = ` `;
  if (title) {
    where += ` and title like '%${title}%' `;
  }
  if (operName) {
    where += `  and oper_name like '%${operName}%' `;
  }
  if (businessType) {
    where += `   and business_type = ${businessType} `;
  }
  if (status) {
    where += `   and status = ${status} `;
  }
  if (startTime & endTime) {
    where += `   and oper_time BETWEEN ${startTime}  and ${endTime}  `;
  }
  let where2 =
    where +
    `  order by oper_time desc ;`;
  const sqlString = sql[table].getOperationLog + where2 ;
  var pool = mysqlDB;
  pool.getConnection((err, connection) => {
    if (!err) {
      connection.query(sqlString, async (err, result) => {
        if (err) {
          console.log(err);
          res.json({
            code: "300",
            msg: "操作失败:",
          });
        } else {
          if (result != "") {
            const sheetName = "Sheel1";
            const heards = [
              { header: "日志编号", key: "operId", width: 15 },
              { header: "系统模块", key: "title", width: 20 },
              { header: "操作类型", key: "businessType", width: 20 },
              { header: "请求方式", key: "requestMethod", width: 20 },
              { header: "请求地址", key: "operUrl", width: 20 },
              { header: "状态", key: "status", width: 15 },
              { header: "账号名称", key: "operName", width: 15 },
              { header: "请求ip", key: "operIp", width: 15 },
              { header: "操作终端", key: "operatorType", width: 15 },
              { header: "请求参数", key: "operParam", width: 24 },
              { header: "返回参数", key: "jsonResult", width: 24 },
              { header: "操作时间", key: "operTime", width: 24 },
            ];
            result.map((item) => {
              item.operTime = formatDateTime(item.operTime);
              item.status = item.status = 1 ? "成功" : "失败";
              item.businessType = item.businessType == 1 ? '新增' : item.businessType == 2 ? '修改' : item.businessType == 3 ? '删除' : '其他'
              item.operatorType = item.operatorType == 2? '手机端用户': item.operatorType == 1 ? '后台用户' : '其它';
              return item;
            });
            // // 将列表数据转成excel表格二进制
            let buffer = await dataToExcel(sheetName, heards, result);
            // 直接保存文件
            // fs.writeFileSync("./data4.xlsx", buffer);
            res.send(buffer);
          } else {
            res.send("没有数据下载");
          }
        }
        connection.release();
      });
    } else {
      console.log(err);
      json(res, "444", err);
    }
  });
};
//视觉识别程序是否开启------------------------------------
let ifrunning = (appname, res) => {
  isRunning(appname, (status) => {
    if (status) {
      res.json({
        code: '200',
        msg: "程序启动成功！",
      })
    } else {
      res.json({
        code: '250',
        msg: "程序未运行",
      })
    }
  })
};
//开启视觉识别------------------------------------
let startCollect = (dir, paths, pathname, res) => {
  // var command =  `D: && cd D:\paige_main && paige_main.exe`
  var command = `${dir} && cd ${dir}\\${paths} && ${pathname}.exe`
  isRunning(pathname, (status) => {
    if (!status) {
      exe.Controlexe(command, res, (status) => { })
      return
    } else {
      command = `taskkill /f /t /im ${pathname}.exe`
      exe.Controlexe(command, res, (status) => { })
    }
  })
  res.json({
    code: '200',
    msg: "请求成功",
  })
};




/**
 * @description 遍历数据的值
 * @param {obj} obj 包含参数的对象
 * */
let paramList = (obj) => {
  let paramArr = [];
  for (let key in obj) {
    if (obj[key]) {
      paramArr.push(obj[key]);
    }
  }
  return paramArr;
};

module.exports = {
  login,
  loginOut,
  getRoutes,
  getOperationPermissions,
  getMenu,
  updateMenu,
  addMenu,
  delMenu,
  register,
  getAccount,
  getRole,
  delRole,
  updateRole,
  addRoleName,
  addRole,
  listRole,
  roleMenuTreeselect,
  getAllRoute,
  EditAccount,
  DeleteAccount,
  UpdateUserinfo,
  updatepwd,
  updateAvatar,
  getUser,
  getUserRole,
  updateUserPermission,
  changeRoleStatus,
  getlogininfor,
  deleteLoginInfo,
  getOperationLog,
  deleteOperLog,
  exportLoginLogExcel,
  exportOperLogExcel,
  startCollect,
  ifrunning,
};